USE [msdb]
GO

/****** Object:  Job [Создание счетов новым абонентам]    Script Date: 05/16/2012 18:00:31 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/16/2012 18:00:32 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Создание счетов новым абонентам', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Если клиент не использует счет РМ для расчетов, то он всеравно создается, т.к. шеф хочет...', 
		@category_name=N'[Uncategorized (Local)]', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step 1]    Script Date: 05/16/2012 18:00:36 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'SET NOCOUNT ON;

-- Определяем клиентов
DECLARE @iError int, @iPhomeBad int, @iPhomeEdit int, @iPhomeTotal int,
		@sPhone	varchar(20), @sPhoneCheck varchar(20), @sResult varchar(20);

SET @iError = 0;
SET @iPhomeBad = 0;

SELECT	al.Phone, MIN(al.[datetime]) AS FirstPay, MAX(al.[datetime]) AS LastPay
INTO	#TempPhone 
FROM	[i_dealer].[dbo].[AutomatLog] as al 
WHERE	al.DEMO = 0 and al.Prev_id IS NULL AND 
		CAST(ISNULL(al.[SUM],0) AS MONEY) > 0 AND al.Phone NOT LIKE ''7000%'' AND  
		al.Phone NOT LIKE ''000%'' AND LEN(RTRIM(al.Phone)) >= 11 AND
		NOT EXISTS (SELECT	PhoneId							-- Если счет уже имеется, то не берем
					FROM	Wallet.dbo.Phones as p 
					WHERE	al.Phone = p.PhoneNumber 
					)
group by al.Phone

SET @iPhomeTotal = @@ROWCOUNT;

PRINT ''Общее количество записей '' + CAST( @iPhomeTotal as varchar(12))
PRINT ''Проверка на легитимность'';

DECLARE VerifyCurs CURSOR LOCAL FAST_FORWARD 
FOR -- Выбрать все 
	SELECT	Phone
	FROM	#TempPhone

-- Проверка телефонов на легитимность
OPEN VerifyCurs;
FETCH NEXT FROM VerifyCurs	INTO 	@sPhone
WHILE @@FETCH_STATUS = 0		
BEGIN
	exec sms_p.dbo.sp_CheckAddress7Plus @sPhone, ''Automat'', @sPhoneCheck OUT, @sResult OUT;

	IF @sResult != ''OK''					-- Ecли проверка не ОК
	BEGIN
		SET @iPhomeBad = @iPhomeBad + 1;

		delete	#TempPhone
		where	Phone = @sPhone

		SET @iError = @@ERROR;

		IF @iError != 0
		BEGIN
			PRINT ''Ошибка удаления '' + @sPhone;
			BREAK;
		END
		PRINT ''Отсеян '' + @sPhone;
	END
	ELSE IF @sPhone != @sPhoneCheck		-- Ecли проверка ОК и номер изменился
	BEGIN
		SET @iPhomeEdit = @iPhomeEdit + 1;

		update	#TempPhone
		set		Phone = @sPhoneCheck
		where	Phone = @sPhone

		SET @iError = @@ERROR;

		IF @iError != 0
		BEGIN
			PRINT ''Ошибка обновления '' + @sPhone;
			BREAK;
		END
	END

	FETCH NEXT FROM VerifyCurs	INTO 	@sPhone
END

CLOSE VerifyCurs;
DEALLOCATE VerifyCurs;

PRINT ''Количество отсеянных '' + CAST( @iPhomeBad as varchar(12));
PRINT ''Количество исправленных '' + CAST( @iPhomeEdit as varchar(12));

IF @iError = 0
BEGIN
	-- удалим телефоны уже имеющие счета
	PRINT ''Отсев номеров, уже имеющих счета'';

	delete #TempPhone
	WHERE EXISTS (	SELECT	PhoneId 
					FROM	Wallet.dbo.Phones as p 
					WHERE	#TempPhone.Phone = p.PhoneNumber )

	PRINT ''Количество номеров, имеющих счета '' + CAST( @@ROWCOUNT as varchar(12));
	PRINT ''Добавление счетов'';

	DECLARE @dDateFirst datetime, @dDateLast datetime, @iRID bigint, @iCounter int ;

	SET @iRID = 0;
	SET @iCounter = 0;

	DECLARE VerifyCurs CURSOR LOCAL FAST_FORWARD 
	FOR -- Выбрать все 
		SELECT	t.Phone, MIN(t.FirstPay), MAX(t.LastPay)
		FROM	#TempPhone as t 
		GROUP BY t.Phone
	
	OPEN VerifyCurs;
	FETCH NEXT FROM VerifyCurs	INTO 	@sPhone, @dDateFirst, @dDateLast
	WHILE @@FETCH_STATUS = 0		
	BEGIN
		-- Запись нового счета
--		PRINT @iCounter;
		BEGIN TRAN
		BEGIN TRY
			INSERT	Wallet.dbo.Phones(PhoneNumber, DefaultSub, FistTime)
			VALUES( @sPhone, 6, @dDateFirst)

			SET @iRID = @@IDENTITY;

			IF @@ERROR = 0 AND @iRID IS NOT NULL
			BEGIN
				INSERT	Wallet.dbo.Accounts(PhoneId, BankAndAccount, Summary, LastTime, Sub, FirstTime, [Enabled])
				VALUES(	@iRID, @sPhone + ''#6'', 0, @dDateLast, 6, @dDateFirst, 1)
		
				IF @@ERROR = 0
				BEGIN
					COMMIT TRAN;
					SET @iCounter = @iCounter + 1;
				END
				ELSE
				BEGIN
					ROLLBACK TRAN;
					PRINT ''Error Insert Wallet.dbo.Accounts ''+@sPhone;
					BREAK;
				END
			END
			ELSE
			BEGIN
				ROLLBACK TRAN;
				PRINT ''Error Insert Wallet.dbo.Phone ''+@sPhone + '' ''+ CAST(@iRID as varchar(12));
				BREAK;
			END
		END TRY
		BEGIN CATCH
			PRINT ERROR_MESSAGE();
			PRINT ERROR_NUMBER();
			PRINT ERROR_SEVERITY();
			PRINT ERROR_STATE();

			ROLLBACK TRAN;
			PRINT ''Error Insert ''+@sPhone;
			BREAK;
		END CATCH

		FETCH NEXT FROM VerifyCurs	INTO 	@sPhone, @dDateFirst, @dDateLast
	END

	CLOSE VerifyCurs;
	DEALLOCATE VerifyCurs;
	
	PRINT ''Количество добавленных счетов '' + CAST(@iCounter as varchar(12));

END

drop table #TempPhone', 
		@database_name=N'master', 
		@database_user_name=N'ALTER-I\AdministratorDB', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule 1', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20070627, 
		@active_end_date=99991231, 
		@active_start_time=43000, 
		@active_end_time=235959, 
		@schedule_uid=N'd8053730-7ea3-4978-ac6b-f0a1844aef11'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

